﻿/*
=============================================================================
Author:魏宝辉通过CodeSmithe生成
CreatedTime:2012-11-3
Description:CQuery_ReportList.aspx.cs页面代码
=============================================================================	
*/

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Web;
using FineUI;
using FTchina.Library;
using FTchina.Logic;
using FTchina.Models;
using PWMIS.Common;
using PWMIS.DataMap.Entity;
using PWMIS.DataProvider.Adapter;
using PWMIS.DataProvider.Data;
using AspNet = System.Web.UI.WebControls;
public partial class CQuery_ReportListaction : BasePage
{


    protected void Page_Load(object sender, EventArgs e)
    {
        string id = RequestHelper.SafeRequest(Request["Rid"]);
        //LogHelper.Debug("Page_Load" + id);

        if (txbguid.Text == "")
        {//新建，需要统一guid
            txbguid.Text = Guid.NewGuid().ToString();
        }

        if (!IsPostBack)
        {
            LoadData();
            if (id.Length > 0)
            {
                ShowModData(id);
            }
        }

    }
    private void LoadData()
    {

        // 关闭按钮的客户端脚本：
        // 1. 首先确认窗体中表单是否被修改（如果已经被修改，则弹出是否关闭的确认对话框）
        // 2. 然后关闭本窗体，回发父窗体
        btnClose.OnClientClick = ActiveWindow.GetConfirmHidePostBackReference();
        ControlBindHelper.DropDownListBind(ddlconnId, "CQuery_ConnString", "ConnName");
        if (MasterHelper.IsAdmin)
        {//当前用户为管理员
            ControlBindHelper.DropDownListBind(ddlFormID, "CQuery_FormList", "ReportName");
        }
        else
        {
            ControlBindHelper.DropDownListBindWithPermissions(ddlFormID, "CQuery_FormList", "ReportName");
        }
        ddlFormID.Items.Add("无", "Empty");


        if (MasterHelper.IsAdmin)
        {//当前用户为管理员
            ControlBindHelper.DropDownListBind(ddlLinkReportID, "CQuery_ReportList", "ReportName");
        }
        else
        {
            ControlBindHelper.DropDownListBindWithPermissions(ddlLinkReportID, "CQuery_ReportList", "ReportName");
        }
        ddlLinkReportID.Items.Add("无", "Empty");


        lblshuoming.Text = @"唯一字段将作为反馈表单的标识字段"
                            + "<br>非替换模式是指在“SQL语句”栏中语句的基础上 增加 where 条件："
                            + "<br>替换模式是指将“SQL语句”栏中语句进行替换，需要替换的语句必须为“字段={值}”格式，只有符合此格式字段才能作为查询控件"
                            + "<br>默认值可用关键字："
                            + "$姓名$=" + MasterHelper.UName
                            + ",$账户$=" + MasterHelper.UID
                            + ",$Now$=" + DateTime.Now.ToString()
                            + "<br>下拉列表默认值样例：男^1,女^0 其中，显示为男、女，值为1、0"
                            + "<br>下拉列表默认项有\"为空\"、\"不选择\"两个：为空=(is Null or x='');不选择=忽略本项";
    }
    private void ShowModData(string ItemID)
    {

        FTchina.Models.CQuery_ReportList mCQuery_ReportList = FTchina.BLL.CQuery_ReportListManager.GetCQuery_ReportListById(Convert.ToInt32(ItemID));
        if (mCQuery_ReportList != null)
        {
            txbid.Text = mCQuery_ReportList.Id.ToString().Trim();
            ddlconnId.SelectedValue = mCQuery_ReportList.ConnID;
            txbreportName.Text = mCQuery_ReportList.ReportName.ToString().Trim();
            txbshortView.Text = mCQuery_ReportList.ShortView.ToString().Trim();
            txbsqlString.Text = mCQuery_ReportList.SqlString;

            txbguid.Text = mCQuery_ReportList.Guid;//将本次的GUID赋值，供其他地方使用
            ddlFormID.SelectedValue = mCQuery_ReportList.FormID;
            ddlLinkReportID.SelectedValue = mCQuery_ReportList.LinkReportID;
            ddlReplaceMode.SelectedValue = mCQuery_ReportList.ReplaceMode;
            #region 将相关的列都查出来
            Cquery_ColumList ccl = new Cquery_ColumList();
            OQLCompare cmp = new OQLCompare(ccl);
            OQLCompare cmpresult = (cmp.Comparer(ccl.Guid, OQLCompare.CompareType.Equal, mCQuery_ReportList.Guid)
                & cmp.Comparer(ccl.Isinuse, OQLCompare.CompareType.Equal, "1")
            );//guid isinuse 过滤条件

            OQL q = new OQL(ccl);
            q.Select().Where(cmpresult).OrderBy(ccl.Paixu, "ASC");
            List<Cquery_ColumList> Modlist = EntityQuery<Cquery_ColumList>.QueryList(q, ccl);
            ViewState["ModeList"] = Modlist;//放入ViewState

            Grid1.RowDataBound += new EventHandler<GridRowEventArgs>(Grid1_RowDataBound);
            Grid1.DataSource = Modlist;
            Grid1.DataBind();

            FillDDLColum();//绑定排序字段
            ddlOrderColum.SelectedValue = mCQuery_ReportList.OrderColum;

            banishSelectLoop(mCQuery_ReportList);//避免循环父子关联

            #endregion
        }
        else
        {
            AlertLogic.ShowResultAndRedirect("无数据！", "提示", HttpContext.Current.Request.Path.ToString(), false);
        }
    }


    /// <summary>
    /// 处理下循环关联的问题。自身不能选，关联宿主不能选
    /// </summary>
    /// <param name="mCQuery_ReportList"></param>
    private void banishSelectLoop(FTchina.Models.CQuery_ReportList mCQuery_ReportList)
    {
        ddlLinkReportID.Items.FindByValue(mCQuery_ReportList.Id.ToString()).EnableSelect = false;//不能选择自身，否则会循环关联

        CQuery_ReportList crl = new CQuery_ReportList();
        OQLCompare cmpx = new OQLCompare(crl);
        OQLCompare cmpresultx = (cmpx.Comparer(crl.LinkReportID, OQLCompare.CompareType.Equal, mCQuery_ReportList.Id.ToString())

        );//guid isinuse 过滤条件

        OQL qx = new OQL(crl);
        qx.Select().Where(cmpresultx);
        List<CQuery_ReportList> Modlistx = EntityQuery<CQuery_ReportList>.QueryList(qx, crl);
        foreach (CQuery_ReportList item in Modlistx)
        {
            ddlLinkReportID.Items.FindByValue(item.Id.ToString()).EnableSelect = false;//不能选择自身，否则会循环关联  
        }
    }


    protected void btnSaveContinue_Click(object sender, EventArgs e)
    {
        // 1. 这里放置保存窗体中数据的逻辑
        FTchina.Models.CQuery_ReportList mCQuery_ReportList = new FTchina.Models.CQuery_ReportList();
        mCQuery_ReportList.Id = txbid.Text.Trim() == "" ? 0 : Convert.ToInt32(txbid.Text);
        if (mCQuery_ReportList.Id == 0)
        {//第一次添加是创建，修改不能动
            mCQuery_ReportList.Guid = txbguid.Text;
            mCQuery_ReportList.LuruId = MasterHelper.Luruid;
        }
        mCQuery_ReportList.ConnID = ddlconnId.SelectedValue;
        mCQuery_ReportList.ReportName = txbreportName.Text.Trim();
        mCQuery_ReportList.ShortView = txbshortView.Text.Trim();
        if (ddlReplaceMode.SelectedValue == "0")
        {//非替换模式，sql语句中不能有{}替换符合
            mCQuery_ReportList.SqlString = ReportLogic.RemoveFormater(txbsqlString.Text);
        }
        else
        {//替换模式直接保存
            mCQuery_ReportList.SqlString = txbsqlString.Text;
        }
        mCQuery_ReportList.OrderColum = ddlOrderColum.SelectedValue;
        mCQuery_ReportList.FormID = ddlFormID.SelectedValue;
        mCQuery_ReportList.LinkReportID = ddlLinkReportID.SelectedValue;
        mCQuery_ReportList.ReplaceMode = ddlReplaceMode.SelectedValue;
        if (FTchina.BLL.CQuery_ReportListManager.AddOrUpdateCQuery_ReportList(mCQuery_ReportList) > 0)
        {//报表信息保存成功，才能保存列的信息
            // LogHelper.Debug("报表信息保存成功，才能保存列的信息");

            SaveColums();
            ReportLogic.UpdateSqlString(txbguid.Text, mCQuery_ReportList.SqlString);

        }
        else
        {
            // LogHelper.Debug("报表信息保存成功，才能保存列的信息----失败");
        }
        // 2. 关闭本窗体，然后回发父窗体
        PageContext.RegisterStartupScript(ActiveWindow.GetHidePostBackReference());
    }

    /// <summary>
    /// 绑定排序字段
    /// </summary>
    protected void FillDDLColum()
    {

        ddlOrderColum.DataSource = (List<Cquery_ColumList>)ViewState["ModeList"];
        ddlOrderColum.DataTextField = "ColumID";
        ddlOrderColum.DataValueField = "ColumID";
        ddlOrderColum.DataBind();
        if (ddlOrderColum.Items.Count == 0)
        {
            ddlOrderColum.Items.Add("Id", "Id");
        }
    }


    protected void Grid1_RowDataBound(object sender, FineUI.GridRowEventArgs e)
    {
        List<Cquery_ColumList> Modlist = (List<Cquery_ColumList>)ViewState["ModeList"];
        if (Modlist.Count > 0)
        {//如果有需要绑定显示的数据
            for (int i = 0, count = Grid1.Rows.Count; i < count; i++)
            {
                GridRow row = Grid1.Rows[i];
                ((AspNet.TextBox)row.FindControl("txbColumName")).Text = Modlist[i].ColumName;
                ((AspNet.DropDownList)row.FindControl("ddlQueryType")).SelectedValue = Modlist[i].QueryType;
                ((AspNet.CheckBoxList)row.FindControl("cblIsQuery")).SelectedValue = Modlist[i].IsQuery;
                ((AspNet.CheckBoxList)row.FindControl("cblIsShow")).SelectedValue = Modlist[i].IsShow;
                ((AspNet.TextBox)row.FindControl("txbPaixu")).Text = Modlist[i].Paixu.ToString();
                ((AspNet.TextBox)row.FindControl("txbDefaultValue")).Text = Modlist[i].DefaultValue;
            }
        }
    }


    /// <summary>
    /// 生成列按钮
    /// </summary>
    /// <param name="sender"></param>
    /// <param name="e"></param>
    protected void btnGeren_click(object sender, EventArgs e)
    {
        try
        {
            btnClose.Enabled = false;//点击了生成按钮必须重新保存各个列的状态。    

            //初始化数据源模型
            CQuery_ConnString ccs = new CQuery_ConnString();
            ccs.Id = Convert.ToInt64(ddlconnId.SelectedValue);
            EntityQuery<CQuery_ConnString> query = new EntityQuery<CQuery_ConnString>(ccs);
            query.FillEntity(ccs);

            //获取数据源类型
            DBMSType dbtype = DBTypeLogic.getDbmsTypeByString(ccs.DbType);

            ///实例化操作助手
            AdoHelper adohelper = MyDB.GetDBHelper(dbtype, ccs.ConnString);



            string sql = string.Empty;//存放生成的sql
            if (ddlReplaceMode.SelectedValue == "0")
            {//附加条件的方式
                //获取列，取一行数据就行
                string sqlFormat = "select * from ({0}) tt";
                sql = string.Format(sqlFormat, txbsqlString.Text);
            }
            else
            {//替换条件的方式
                sql = txbsqlString.Text;
            }

            sql = ReportLogic.RemoveFormater(sql);//生成列的时候，sql语句要执行，所以去掉替换标记


            string sqlSingle = PWMIS.Common.SQLPage.MakeSQLStringByPage(dbtype, sql, "", 1, 1, 1);

            sqlSingle = ReportLogic.DefaultValueFormat(sqlSingle);//处理下替换值

            //Alert.Show(sqlSingle);
            DataTable dt = adohelper.ExecuteDataSet(sqlSingle).Tables[0];
            //获取字段数据，新增或者提取已设置过的老数据
            List<Cquery_ColumList> list = GetColums(dt);


            if (ddlReplaceMode.SelectedValue == "1")
            {//替换模式要把=提取出来作为条件
                List<Cquery_ColumList> list2 = GetColums(ReportLogic.GetColumsBySql(txbsqlString.Text));//Sql语句中获取字段
                List<Cquery_ColumList> ReplaceModlist = list2;//赋值给替换模式，在保存数据时对显示、查询属性进行更正，只有替换模式中的字段可以为查询
                ViewState["ReplaceModlist"] = list2;//放入ViewState
                list.AddRange(list2);
            }

            ViewState["ModeList"] = list;//把新的列表数据赋值给公用的属性，绑定时调用
            Grid1.RowDataBound += new EventHandler<GridRowEventArgs>(Grid1_RowDataBound);

            Grid1.RecordCount = list.Count;
            Grid1.DataSource = list;
            Grid1.DataBind();

            FillDDLColum();//绑定排序字段

            //Alert.Show(adohelper.ExecuteDataSet("select * from sms_log").Tables[0].Rows.Count.ToString());
        }
        catch (Exception er)
        {
            AlertLogic.ShowError(er.Message);
        }
    }

    /// <summary>
    /// 提取字段数据
    /// </summary>
    /// <param name="NewColumsdt"></param>
    /// <returns></returns>
    private List<Cquery_ColumList> GetColums(DataTable NewColumsdt)
    {
        List<Cquery_ColumList> tmp = new List<Cquery_ColumList>();

        #region 获取数据库中已有的字段集合

        Cquery_ColumList cclist = new Cquery_ColumList();
        OQLCompare cmp = new OQLCompare(cclist);
        OQLCompare cmpresult = cmp.Comparer(cclist.Guid, OQLCompare.CompareType.Equal, txbguid.Text);
        OQL q = new OQL(cclist);
        q.Select().Where(cmpresult).OrderBy(cclist.Paixu, "ASC");
        List<Cquery_ColumList> Modlist = EntityQuery<Cquery_ColumList>.QueryList(q, cclist);
        #endregion

        #region 比对所有的字段，找到原有的字段，新加的字段赋值给AddColums
        if (Modlist.Count > 0)
        {//数据库中有原来的数据
            foreach (DataColumn dc in NewColumsdt.Columns)
            {

                if (dc.ColumnName.ToUpper() == "R_N") continue;
                foreach (Cquery_ColumList ccl in Modlist)
                {
                    if (ccl.ColumID == dc.ColumnName.ToString().ToUpper())
                    {//如果匹配，说明是老数据，提取
                        tmp.Add(ccl);
                    }
                }
                //第一次循环把老数据添加完毕，再跑一次比对新数据，要不然会重复
                int i = 1;
                foreach (Cquery_ColumList ccl in Modlist)
                {
                    if (ccl.ColumID != dc.ColumnName.ToString().ToUpper())
                    {
                        
                        Cquery_ColumList cclx = new Cquery_ColumList();
                        cclx.Id = 0;
                        cclx.ColumID = dc.ColumnName.ToString().ToUpper();
                        cclx.Paixu = i*10;
                        cclx.IsQuery = "1";
                        cclx.IsShow = "1";
                        cclx.Isinuse = "1";

                        if (!tmp.Contains(cclx, ColumIDComparer.Default))
                        {//去重
                            tmp.Add(cclx);
                        }
                        i++;
                    }
                }
            }
        }
        else
        {//如果原来没有的话，那就全部放到新加
            int j = 1;
            foreach (DataColumn dc in NewColumsdt.Columns)
            {
                if (dc.ColumnName.ToUpper() == "R_N") continue;
                Cquery_ColumList cclx = new Cquery_ColumList();
                cclx.Id = 0;
                cclx.ColumID = dc.ColumnName.ToString().ToUpper();
                cclx.Paixu = j*10;
                cclx.IsQuery = "1";
                cclx.IsShow = "1";
                cclx.Isinuse = "1";
                j++;
                tmp.Add(cclx);

            }
        }
        #endregion

        #region 将没有用到的字段更新Isinuse
        IEnumerable<Cquery_ColumList> nouselist = Modlist.Union<Cquery_ColumList>(tmp).Except<Cquery_ColumList>(tmp);

        foreach (Cquery_ColumList itemx in nouselist)
        {
            itemx.Isinuse = "0";

            EntityQuery<Cquery_ColumList> query = new EntityQuery<Cquery_ColumList>(itemx);
            query.Update(itemx);
        }
        #endregion

        return tmp;
    }

    /// <summary>
    /// 保存列
    /// </summary>
    protected void SaveColums()
    {
        List<Cquery_ColumList> ReplaceModlist = (List<Cquery_ColumList>)ViewState["ReplaceModlist"];
        if (ddlReplaceMode.SelectedValue == "1" && ReplaceModlist == null)
        {
            Alert.Show("替换模式每次保存前请先点击生成列按钮。");
            return;
        }


        string OrderColum = ddlOrderColum.SelectedValue;//排序字段，必须要展示

        for (int i = 0, count = Grid1.Rows.Count; i < count; i++)
        {
            Cquery_ColumList ccl = new Cquery_ColumList();

            GridRow row = Grid1.Rows[i];

            object[] rowDataKeys = Grid1.DataKeys[i];
            ccl.ColumID = rowDataKeys[1].ToString();
            ccl.Isinuse = "1";
            ccl.Id = Convert.ToInt64(rowDataKeys[0]);
            // LogHelper.Debug(ccl.Id + "--" + ccl.Isinuse + "--" + ccl.ColumID);
            ccl.Guid = txbguid.Text;



            AspNet.DropDownList _ddlQueryType = (AspNet.DropDownList)row.FindControl("ddlQueryType");
            ccl.QueryType = _ddlQueryType.SelectedValue;

            AspNet.TextBox _txbColumName = (AspNet.TextBox)row.FindControl("txbColumName");
            ccl.ColumName = _txbColumName.Text;

            AspNet.CheckBoxList _cblIsQuery = (AspNet.CheckBoxList)row.FindControl("cblIsQuery");
            if (_cblIsQuery.SelectedItem == null)
            {
                ccl.IsQuery = "0";
            }
            else
            {
                ccl.IsQuery = _cblIsQuery.SelectedItem.Value;
            }

            if (ccl.IsQuery == "1")
            {//如果是查询列，那么默认也是可显示，避免某些数据库查询字段 根 搜索字段 不匹配
                ccl.IsShow = "1";
            }
            else
            {//如果不是查询列，那么显示列自由
                AspNet.CheckBoxList _cblIsShow = (AspNet.CheckBoxList)row.FindControl("cblIsShow");
                if (_cblIsShow.SelectedItem == null)
                {
                    if (ccl.ColumID == OrderColum)
                    {//排序字段必须显示
                        ccl.IsShow = "1";
                    }
                    else
                    {
                        ccl.IsShow = "0";
                    }
                }
                else
                {
                    ccl.IsShow = _cblIsShow.SelectedItem.Value;
                }
            }

            if (ddlReplaceMode.SelectedValue == "1" && ReplaceModlist != null)
            {//已经赋值了才能使用，空的说明没有sql字段
                foreach (Cquery_ColumList ccrm in ReplaceModlist)
                {
                    if (ccrm.ColumID == ccl.ColumID)
                    {//替换模式中获取的字段必须要作为查询，要替换SQL语句中的字段。，但是不能显示
                        ccl.IsShow = "0";
                        ccl.IsQuery = "1";


                    }
                    else
                    {
                        // LogHelper.Debug("条件以外的字段都不能查询");
                        ccl.IsQuery = "0";//sql条件以外的字段都不能查询
                    }

                }
            }


            AspNet.TextBox _txbPaixu = (AspNet.TextBox)row.FindControl("txbPaixu");
            ccl.Paixu = Convert.ToInt64(_txbPaixu.Text);

            AspNet.TextBox _txbDefaultValue = (AspNet.TextBox)row.FindControl("txbDefaultValue");
            ccl.DefaultValue = _txbDefaultValue.Text;

            FTchina.BLL.Cquery_ColumListManager.AddOrUpdateCquery_ColumList(ccl);
        }




    }
    public class ColumIDComparer : IEqualityComparer<Cquery_ColumList>
    {
        public static ColumIDComparer Default = new ColumIDComparer();
        #region IEqualityComparer<PopupModel> 成员
        public bool Equals(Cquery_ColumList x, Cquery_ColumList y)
        {
            return x.ColumID.Equals(y.ColumID);
        }
        public int GetHashCode(Cquery_ColumList obj)
        {
            return obj.GetHashCode();
        }
        #endregion
    }



}






